import sqlite3
import pymysql

def get_conn():
    #由于mysql和sqlite的sql语句差不多，所以可以直接更换为sqlite3数据库
    # con = sqlite3.connect(r'C:\fpga\fpga.db')
    # cur = con.cursor()
    # con = pymysql.connect(host="127.0.0.1", user="root", password="hhhfffyyy", db="fpga", charset="utf8")
    con = pymysql.Connect(
        host='bj-cynosdbmysql-grp-891xzjiy.sql.tencentcdb.com',  # 外网地址  (数据库管理中查看)
        port=23394,  # 外网端口  (数据库管理中查看)
        user='user',  # 账号      (初始化的账号)
        passwd='NCEPUmysql2021',  # 密码      (初始化的密码)
        db='datagear'  # 数据库名称
    )
    # c创建游标A
    cur = con.cursor()
    return con, cur

con,cur=get_conn()
def insert_MYSQL_DB(t, x, y):
    #单条插入
    t_str=str(t)
    x_str=str(x)
    y_str=str(y)
    sql = 'INSERT INTO `fpga` (`t`, `x`, `y`) VALUES ( ' + t_str + ' , ' + x_str + ' , ' + y_str + ' )'
    cur.execute(sql)
    con.commit()
        # print_gui('数据库写入成功')
        # print_gui(sql)


# def insertDB_data(data):
#     # print(data)
#     #批量插入
#     try:
#         #批量插入中，与sql 稍微有点区别的是，sqlite 是使用的 ? 作为占位符，而不是 %s，%d
#         sql = 'INSERT INTO `fpga` VALUES (?,?,?)'
#         cur.executemany(sql,data)
#         con.commit()
#         print('数据库写入成功')
#     except sqlite3.Error as e:
#         print("Error %d: %s" % (e.args[0], e.args[1]))

def db_clear():
    sql = 'delete from fpga'
    cur.execute(sql)
    con.commit()
    # print_gui('清空数据库成功！')
    # 插入一条0数据防止程序BUG
    sql = 'INSERT INTO `fpga` VALUES (0.0,0,0.0)'
    cur.execute(sql)
    con.commit()
    # print_gui(sql+'   (插入一条值为0的数据，防止空数据导致matplotlib绘图闪退)')




def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()
def query(sql,*args):
    """
    :param sql:
    :param args:
    :return:
    """
    conn,cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res
def get_c1_data():
    sql = "select one,two,three,four from management"
    res = query(sql)
    return res
############################
#以下皆为旧版查询数据库，已经废弃#
############################

#
# def get_c2_data():
#     sql = ""
#     res = query(sql)
#     return res
#
# def get_l1_data():
#     sql = "select b.x,b.y  from (select t,x,y from fpga order by t desc limit 10000)b  order by b.t"
#     res = query(sql)
#     return res
#
# def get_l2_data():
#     sql = "select b.x,b.y  from (select t,x,y from fpga order by t desc)b  order by b.t"
#     res = query(sql)
#     return res
#
# def get_r1_data():
#     sql = "select y from fpga order by t desc limit 1"
#     res = query(sql)
#     return res
#
# def get_r2_data():
#     sql = "select y from fpga order by t desc limit 1"
#     res = query(sql)
#     return res
#
# def get_fpga_last_data():
#     #查询最新一条数据
#     sql =  "select y from fpga order by t desc limit 1"
#     res = query(sql)
#     return res
